Student Solution

-->

"Education is the most powerful weapon which you can use to change the world”
– Nelson Mandela

1 University

1 Course

1 Subject

Excel Project 1

Excel Project

Q Instructions Excel Project 1 This Excel assignment will have you classify a number of financial accounts into categories based on which financial statement they can be found. For example, the cash account is an asset and is found in the balance sheet; the rent expense account is an expense which is found in the income statement. Module Objectives 1 and 2, and Course Objectives 1 and 2 are covered in this Module 1 assignment. Instructions: 1. Download and read the Excel Project 1 instructions and grading rubric (the file link is shown at the bottom of this page). 2. The chart of accounts that you will need can be found on Page S-3 in the back of your ACC 200 textbook and can also be found in the file link below. 3. The link to the Excel file you are to use for Excel Project 1 is shown below. 4. You will first classify each account using a numerical system in the Excel file (1 = Asset, 2 = liability, etc.). Here are some hints to help you classify some of the accounts: 1. An account name with the word "Payable" on the end is a liability. 2. These are revenue accounts even though not so apparent: Sales Discounts, Sales Returns, Sales Allowances, and Gain. 3. These are expense accounts even though not so apparent: Cost of Good Sold and Loss. 4. These are assets accounts: Allowance for Uncollectible Accts. and Accumulated Depreciation. 5. An account name with the word "Stock" on the end is a stockholders' equity account. 5. Next use the Excel IF function to associate an account that is found in either the balance sheet, the income statement or the statement of changes in stockholders' equity (explained in the instructions referred to in Part 1 above, as well as demonstrated in this module's lecture video). 6. Finally, create a chart of accounts in the places provided in the second worksheet in the Excel file. ACC200L – Excel Project 1 Instructions: For the purpose of grading the project you are required to perform the following tasks: Step Instructions For Project #1, download the Excel file from Canvas and use it to classify accounts and to create a Chart of Accounts. On Sheet 1 of the file there is a list of accounts that you will be using during the semester in ACC 200. Following the instructions provided, your task is to 1 identify the classification of each account and the financial statement that the account can be found in. You may use as a reference the Chart of Accounts from the End Matter section of your ACC 200 textbook [Page S-3 in Financial Accounting 4th edition]. Note: Not all of the accounts from your textbook's Chart of Accounts have been included on this list. You do not need to add them into the list. Please note: Watch the Project 1 Video to better understand this assignment. On the tab labeled "Accounts" of the workbook: 1. In Column B: Enter the appropriate number in Column B that corresponds with the account classification as shown below. Several of the accounts have been done for you as an example. You may simply key in the number (enter it manually). Asset = 1 Liability = 2 Stockholders' Equity = 3 2 Dividends = 4 Revenues = 5 Expenses = 6 2. In Column C: Enter the appropriate number that corresponds with the financial statement as shown below. Several of the accounts have been done for you as an example. You may simply key in the number (enter it manually) Balance sheet = 7 Income statement = 8 Statement of Changes in Stockholders’ Equity = 9 On the tab labeled "Accounts" in Column D: 1. Using the numbering scheme that you input in Column B for each account, in Column D, use an "=IF" formula to identify the classification for each account. For this formula, compare the cell contents of Column B to a number, and if true, the result should come from Column G. 3 NOTE: YOU MUST HAVE OFFICE 365 TO USE THE NESTED "IF" FUNCTION. IF IT IS NOT INCLUDED IN YOUR FUNCTIONS, GO TO THE NKU WEBSITE FOLLOW THE INSTRUCTIONS TO DOWNLOAD OFFICE 365. For example: =IF(B2=1,$G$3) which means if the number in Cell B2 equals 1, then enter the word found in Cell G3. 2. Once you have created the proper formula, you may copy or use the fill feature to replicate the formula for every account. Before you copy/fill the formula, you must edit the formula so Instructions Excel Project 1 Step Instructions that the inputs for Columns G remain constant as shown in class. (F4 key in edit mode or $ before each component of the cell, i.e. $G$3) On the tab labeled "Accounts" in Column E: 1. Using the IF function, determine which financial statement the account belongs to. Using the information in Column C as a reference, determine whether the account belongs on the 4 Balance Sheet, the Income Statement, or Statement of Changes in Stockholders’ Equity. Reference the Chart of Accounts on Page S-3 from the textbook to help you determine the proper financial statement. 2. Create an IF function which compares the value in Column C to the options in Column G. You should identify each account as belonging to the Balance Sheet, Income Statement, or or Statement of Changes in Stockholders’ Equity. Using the information on the "Accounts" tab, create the Chart of Accounts on the "Chart of Accounts" tab by referencing the accounts on the "Accounts" tab. Use the Chart of Accounts from the textbook to order the accounts in the same order as in the text. 5 Note that not all of the accounts from the textbook have been included on the list. You do not need to add them into the list. For example: To complete the first line of the ASSET box, use a cell reference for Cell A3 to populate the cell with the contents of cell A6 on the "Accounts" tab. Continue until you fill each classification box. You will not have excess lines in the boxes. Grading Points will be awarded based on following closely the instructions above and the accuracy of the answers in the student-submitted Excel file. Entering the correct formulas in the cells is Rubric very important and must both follow the instructions above and any related instructional videos. Total The total points possible for this project is 25. Points

View Related Questions

Solution Preview

Cash 1 7 Accounts Receivable 1 7 Allowance for Uncollectible Accts. 1 7 Notes Receivable 1 7 Interest Receivable 1 7 Supplies 1 7 Inventory 1 7